var db = require('../../config/db')
//查询
exports.search = (req,res)=>{
  if(req.query.tar == '1'){
    //用户
    const userId = req.query.id;

  // 查询土地资源
  db.query(`SELECT * FROM land_resource WHERE userid=?`, userId, (err, landResources) => {
    if (err) {
      console.error('查询土地资源出错：', err);
      return res.send({
        status: 500,
        message: '服务器错误',
      });
    }

    if (landResources.length < 1) {
      return res.send({
        status: 404,
        message: '无记录',
      });
    }

    let finalResults = [];

    // 遍历土地资源
    Promise.all(landResources.map(landResource => {
      return new Promise((resolve, reject) => {
        // 查询土地块
        db.query(`SELECT * FROM land_parcel WHERE landresourceid=?`, landResource.id, (err, landParcels) => {
          if (err) {
            console.error('查询土地块出错：', err);
            return reject(err);
          }

          if (landParcels.length < 1) {
            return reject('无记录');
          }

          // 遍历土地块
          Promise.all(landParcels.map(landParcel => {
            return new Promise((resolve, reject) => {
              // 查询菜地
              db.query(`SELECT * FROM vegetable_plot WHERE landparcelid=?`, landParcel.id, (err, vegetablePlots) => {
                if (err) {
                  console.error('查询菜地出错：', err);
                  return reject(err);
                }

                if (vegetablePlots.length < 1) {
                  return reject('无记录');
                }

                // 遍历菜地
                Promise.all(vegetablePlots.map(vegetablePlot => {
                  return new Promise((resolve, reject) => {
                    // 查询种植产出
                    db.query(`SELECT * FROM planting_output WHERE vegetableid=?`, vegetablePlot.id, (err, plantingOutputs) => {
                      if (err) {
                        console.error('查询种植产出出错：', err);
                        return reject(err);
                      }

                      // 处理查询结果
                      plantingOutputs.forEach(output => {
                        output.saledate.setDate(output.saledate.getDate() + 1);
                        output.saledate = new Date(output.saledate).toISOString().split('T')[0];
                        output.name = vegetablePlot.name;
                        output.crops = vegetablePlot.crops;
                        output.area = landParcel.area;
                        output.order = landParcel.order;
                        output.address = landResource.address;
                        finalResults.push(output);
                      });

                      resolve();
                    });
                  });
                })).then(resolve).catch(reject);
              });
            });
          })).then(resolve).catch(reject);
        });
      });
    })).then(() => {
      // 所有查询完成后，发送响应
      res.send({
        status: 200,
        message: '查询成功',
        data: finalResults,
      });
    }).catch(error => {
      console.error('查询过程中出错：', error);
      res.send({
        status: 500,
        message: '服务器错误',
      });
    });
  });
  }else{
    //管理员
  db.query(`select * from planting_output`, async (err, results) => {
    try {
        for (let i = 0; i < results.length; i++) {
            results[i].saledate.setDate(results[i].saledate.getDate() + 1);
            results[i].saledate = new Date(results[i].saledate).toISOString().split('T')[0];
            const results1 = await getVegetablePlot(results[i].vegetableid);
            results[i].name = results1[0].name;
            results[i].crops = results1[0].crops;
        
            const results2 = await getLandParcel(results1[0].landparcelid);
            results[i].area = results2[0].area;
            results[i].order = results2[0].order;
        
            const results3 = await getLandResource(results2[0].landresourceid);
            //results[i].resourcename = results3[0].name;
            results[i].address = results3[0].address;
        }
        res.send({
            status: 200,
            message: '查询成功',
            data: results,
        });
    } catch (error) {
      res.status(500).send({
        status: 500,
        message: '查询失败',
        error: error,
      });
    }
  });
  const getVegetablePlot = (vegetableId) => {
    return new Promise((resolve, reject) => {
      db.query(`select * from vegetable_plot where id=?`, vegetableId, (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      });
    });
  };
  const getLandParcel = (landParcelId) => {
    return new Promise((resolve, reject) => {
      db.query(`select * from land_parcel where id=?`, landParcelId, (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      });
    });
  };
  const getLandResource = (landResourceId) => {
    return new Promise((resolve, reject) => {
      db.query(`select * from land_resource where id=?`, landResourceId, (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      });
    });
  };
  }
}
//添加
exports.add =  (req , res)=>{
  if(req.body.tar == '1'){
    db.query(`select * from land_resource where userid=?`, [req.body.userid],(err, results) => {
      if (err) {
        return res.send({
          status: '500',
          message: '服务器错误',
        });
      }
      if(results.length <1){
        return res.send({
          status: '404',
          message: '添加失败：土地资源信息为空，请先添加',
        });
      }
      try {
        let finalResults = [];
        results.forEach((item, index) => {
          db.query(`select * from land_parcel where landresourceid=?`, results[index].id,(err, results1) =>{
            if(results1.length <1){
              return res.send({
                status: '404',
                message: '添加失败：土地块信息为空，请先添加',
              });
            }
            results1.forEach((item1, index1) =>{
              //
              db.query(`select * from vegetable_plot where landparcelid=?`, results1[index1].id,(err, results2) =>{
                if(results2.length <1){
                  return res.send({
                    status: '404',
                    message: '添加失败：菜地信息为空，请先添加',
                  });
                }
                finalResults.push(...results2);
                if(index === results.length-1 && index1 === results1.length-1){
                  handleFinalResults(finalResults)
                }
              })
            })
          })
        })
      } catch (error) {
        res.send({
          status: '500',
          message: '服务器错误：' + error.message,
        });
      }
    });
    // 处理最终结果
    function handleFinalResults(finalResults) {
      try {
        const targetPlot = finalResults.find(plot => plot.name == req.body.name && plot.status == '是');
        if (!targetPlot) {
          return res.send({
            status: '404',
            message: '添加失败，菜地不存在或正在种植中',
          });
        }else{
          db.query(`select * from planting_output where vegetableid=?`, targetPlot.id,(err, results3) =>{
            if (results3.length > 0) {
              return res.send({
                status: '404',
                message: '添加失败，该菜地的产能数据已存在',
              });
            }else{
                      // 插入操作
              const { id, name, tar, userid, ...rest } = req.body;
              rest.vegetableid = targetPlot.id;
              db.query('INSERT INTO planting_output SET ?', rest,(err, results4) =>{
                res.send({
                  status: 200,
                  message: '添加成功',
                });
              });
            }
          })
        }
      } catch (error) {
        res.send({
          status: '500',
          message: '服务器错误：' + error.message,
        });
      }
    }

    
  }else{
    //管理员
    db.query(`select * from vegetable_plot where name=?`, req.body.name, (err, results) => {
      if(results.length === 1) { // 存在
        if(results[0].status == '否'){
          res.send({
            status: 500,
            message: '添加失败，菜地还在种植中',
          });
        }
        else{
          db.query(`select * from planting_output where vegetableid=?`, results[0].id, (err, results1) => {
            if(results1.length === 1) {
              res.send({
                status: 500,
                message: '添加失败，该菜地的产能数据已经存在',
              });
            } else { // 插入操作
              req.body.vegetableid = results[0].id
              const {id, name, ...rest} = req.body
              db.query('INSERT INTO planting_output SET ?', rest, (err, results2) => {
                if (err) {
                  return res.cc('404', err.message)
                } else {
                  res.send({
                    status: 200,
                    message: '添加成功',
                  });
                }
              });
            }
          });
        }
      } else {
        res.send({
          status: 500,
          message: '添加失败，菜地不存在',
        });
      }
    })

  }
}
//更新
exports.up = (req, res)=>{
  if(req.body.tar == '1'){
    // 使用 Promise 封装数据库查询操作
    const query = (sql, params) => {
      return new Promise((resolve, reject) => {
        db.query(sql, params, (err, results) => {
          if (err) {
            reject(err);
          } else {
            resolve(results);
          }
        });
      });
    };
    // 主逻辑函数
    const main = async () => {
      try {
        let finalResults = [];
        const existingPlots = await query('SELECT * FROM vegetable_plot WHERE name=? AND id=?', [req.body.name, req.body.vegetableid]);

        if (existingPlots.length === 1) { // 未改名
          await query('UPDATE planting_output SET output=?, salesamount=?, saledate=?, remarks=? WHERE id=?', [req.body.output, req.body.salesamount, req.body.saledate, req.body.remarks, req.body.id]);
        } else {
          const landResources = await query('SELECT * FROM land_resource WHERE userid=?', [req.body.id]);

          for (const landResource of landResources) {x
            const landParcels = await query('SELECT * FROM land_parcel WHERE landresourceid=?', [landResource.id]);

            for (const landParcel of landParcels) {
              const vegetablePlots = await query('SELECT * FROM vegetable_plot WHERE landparcelid=?', [landParcel.id]);

              finalResults.push(...vegetablePlots);
            }
          }

          const targetPlot = finalResults.find(plot => plot.name === req.body.name && plot.status === '是');
          if (!targetPlot) {
            return res.send({
              status: '404',
              message: '添加失败，未拥有该菜地或已在种植中',
            });
          }

          const existingOutput = await query('SELECT * FROM planting_output WHERE vegetableid=?', [targetPlot.id]);
          if (existingOutput.length === 1) {
            return res.send({
              status: '404',
              message: '修改失败，该菜地已经存在产量数据',
            });
          }
          await query('UPDATE planting_output SET vegetableid=?, output=?, salesamount=?, saledate=?, remarks=? WHERE id=?', [targetPlot.id, req.body.output, req.body.salesamount, req.body.saledate, req.body.remarks, req.body.id]);
        }
        res.send({
          status: 200,
          message: '修改成功',
        });
      } catch (error) {
        res.send({
          status: 500,
          message: '服务器错误：' + error.message,
        });
      }
    };
    // 调用主逻辑函数
    main();
  }
  else{
    //管理员
    db.query(`select * from vegetable_plot where name=? and id=?`, [req.body.name, req.body.vegetableid], (err, results) => {
      if(results.length ===1){
        db.query('update planting_output  set output=?, salesamount=?, saledate=?, remarks=? WHERE id=?', [req.body.output,req.body.salesamount,req.body.saledate,req.body.remarks, req.body.id], (err, results) =>{
          if(err) return res.cc('404', err.message)
          if(results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
          res.send({
            status: 200,
            message: '修改成功',
          });
        })
      }
      else{
        db.query(`select * from vegetable_plot where name=?`, [req.body.name], (err, results1) =>{
          if(results1.length < 1){
            res.send({
              status: '404',
              message: '修改失败，无该菜地名存在',
            });
          }
          else{
            if(results1[0].status == '否'){
              res.send({
                status: 200,
                message: '修改失败，该菜地正在种植中',
              });
            }
            else{//是否有产单
              db.query(`select * from planting_output where vegetableid=?`, [results1[0].id], (err, results2) =>{
                //
                if(results2.length  ==  1 ){
                  res.send({
                    status: 200,
                    message: '修改失败，该菜地已经存在产量数据',
                  });
                }
                else{
                  db.query('update planting_output  set vegetableid=?,output=?, salesamount=?, saledate=?, remarks=? WHERE id=?', [results1[0].id, req.body.output,req.body.salesamount,req.body.saledate,req.body.remarks, req.body.id], (err, results) =>{
                    if(err) return res.cc('404', err.message)
                    if(results.affectedRows !== 1) return res.cc('500','修改失败：服务器错误')
                    res.send({
                      status: 200,
                      message: '修改成功',
                    });
                  })
                }
              })
  
            }
          }
        })
      }
    })
  }
}
//删除
exports.delete = (req, res)=>{
  db.query(`select * from planting_cost where vegetableid=?`, [req.query.vegetableid], (err, results) => {
    if(results.length == 1){
      res.send({
        status: 500,
        message: '删除失败，存在产能相关的成本数据，请先删除',
    });
    }
    else{
      db.query('DELETE FROM planting_output WHERE id = ?', req.query.id, (err, results1) => {
        if(err) return res.cc('404', err);
        if(results1.affectedRows !== 1) return res.cc('500', '删除失败');
        res.send({
            status: 200,
            message: '删除成功',
        });
    });
    }
  })
}